<?php
# http://projects:8081/cdapp-2kt-api/api/hours_personal_report.php

include_once './_portal.php';

// terminate request ATCOS
if (get_variable('STOP_ALL_NETWORK') === 1) {
  die('Network Stoped');
}

if (get_variable('STOP_ATCOS') === 1) {
  die('ATCOS Stoped');
}

const MIN_TTL = 15 * 60 * 1000;
const MAX_TTL = 20 * 60 * 1000;
const PARAM_NAME = 'hourReportQueryTime';

// const SERVER_ADDRESS = 'http://172.22.107.22/';
// const SERVER_ADDRESS = 'http://172.22.107.21/';
const SERVER_ADDRESS = 'http://172.22.107.24/';

const COLUMNS = [[0, 'number'], [1, 'month'], [2, 'name'], [3, 'rank'], [4, 'qualify'], [5, 'license'], [6, 'seat'], [7, 'sector'], [8, 'post'], [9, 'role'], [10, 'begin'], [11, 'end'], [12, 'minutesDaytime'], [13, 'minutesEvening'], [14, 'minutesNight'], [15, 'minutes'], [16, 'hours'], [17, 'hoursRaw'], [18, 'wage'], [19, 'salary'], [23, 'ratio'], [24, 'unknown']];

$month = isset($_GET['month']) ? $_GET['month'] : date('Y-m');
$orgid = isset($_GET['orgid']) ? $_GET['orgid'] : 13;
$force = isset($_GET['force']) ? $_GET['force'] : 0;

// orgid
// 12: 管制一室, 13: 管制二室

$refresh_info = get_param(PARAM_NAME . $orgid);
$ttl_verify = verify_ttl($refresh_info['tx'], $refresh_info['rx'], MIN_TTL, MAX_TTL);
$info = $force ? 'hourReportQuery: Forced refresh' : 'hourReportQuery: ' . $ttl_verify['info'];
$status = $ttl_verify['status'];
$time = $ttl_verify['time'];

if ($status > 0 || $force) {
  save_param(PARAM_NAME . $orgid, ['tx' => $time, 'rx' => $refresh_info['rx']]);
  $res = main($month, $orgid);
  $time_rx = timestamp();
  save_param(PARAM_NAME . $orgid, ['tx' => $time, 'rx' => $time_rx]);
  $res['info'] = $info;
  output($res);
} else {
  output(['info' => $info]);
}

function main($month, $orgid) {
  $t = timestamp();
  $output = do_query($month, $orgid);
  if ($output['status'] == 'successed') {
    $output['save'] = do_save($output['data']);
  }
  $output['duration'] = timestamp() - $t;
  return [
    // 'session' => $output['session'],
    'month' => $output['month'],
    'orgid' => $output['orgid'],
    'duration' => $output['duration'],
    'status' => $output['status'],
    'count' => $output['count'],
    'missed' => $output['status'] == 'successed' ? count($output['save']['failed']) : -1,
    // 'html' => $output['html'],
  ];
}

function do_save($data) {
  $sqls = [];
  $start = 0;
  $step = 500;
  $stop = 0;
  while ($start < count($data)) {
    if ($start + $step < count($data)) {
      $stop = $start + $step;
    } else {
      $stop = count($data);
    }
    $sql = "INSERT INTO `2kt_hours_report`(`mix`, `number`, `month`, `name`, `rank`, `qualify`, `license`, `seat`, `sector`, `post`, `role`, `begin`, `end`, `minutesDaytime`, `minutesEvening`, `minutesNight`, `minutes`, `hours`, `hoursRaw`, `wage`, `salary`, `ratio`) VALUES ";
    while ($start < $stop) {
      $d = $data[$start];
      $mix = strtotime($d['begin']) . '_' . $d['name'];
      $salary = $d['salary'] == '' ? 0 : str_replace('￥', '', $d['salary']);
      $ratio = $d['ratio'] == '' ? 0 : $d['ratio'];
      $end = $d['end'] == '' ? 'NULL' : "'" . $d['end'] . "'"; // date('Y-m-d H:i:s')
      $minutesDaytime = $d['minutesDaytime'] == '' ? 'NULL' : $d['minutesDaytime'];
      $minutesEvening = $d['minutesEvening'] == '' ? 'NULL' : $d['minutesEvening'];
      $minutesNight = $d['minutesNight'] == '' ? 'NULL' : $d['minutesNight'];
      $minutes = $d['minutes'] == '' ? 'NULL' : $d['minutes'];
      $hours = $d['hours'] == '' ? 'NULL' : $d['hours'];
      $hoursRaw = $d['hoursRaw'] == '' ? 'NULL' : $d['hoursRaw'];

      $sql .= "('{$mix}',{$d['number']},'{$d['month']}','{$d['name']}','{$d['rank']}','{$d['qualify']}','{$d['license']}','{$d['seat']}','{$d['sector']}','{$d['post']}','{$d['role']}','{$d['begin']}',{$end},{$minutesDaytime},{$minutesEvening},{$minutesNight},{$minutes},{$hours},{$hoursRaw},{$d['wage']},{$salary},{$ratio}),";
      $start++;
    }
    $sql = rtrim($sql, ',');
    $sql .= " ON DUPLICATE KEY UPDATE `number` = VALUES(`number`), `end` = VALUES(`end`), `minutesDaytime` = VALUES(`minutesDaytime`), `minutesEvening` = VALUES(`minutesEvening`), `minutesNight` = VALUES(`minutesNight`), `minutes` = VALUES(`minutes`), `hours` = VALUES(`hours`), `hoursRaw` = VALUES(`hoursRaw`), `wage` = VALUES(`wage`), `salary` = VALUES(`salary`), `ratio` = VALUES(`ratio`)";
    $sqls[] = $sql;
  }
  $result = exec_sql_array($sqls);
  $failed = [];
  foreach ($result as $i => $e) {
    if (!$e) {
      $failed[] = $sqls[$i];
      logfile('hours_personal_report::failed_sql', $sqls[$i]);
    }
  }
  return ['result' => $result, 'failed' => $failed];
}

function do_query($month, $orgid) {
  $userlist = get_param('ATCOSUsers');
  $userids = '';
  $usernames = '';
  foreach ($userlist as $user) {
    if ($orgid == 12 && $user['office'] == '管制一室') {
      $userids .= $user['number'] . ',';
      $usernames .= $user['name'] . ',';
    } elseif ($orgid == 13 && $user['office'] == '管制二室') {
      $userids .= $user['number'] . ',';
      $usernames .= $user['name'] . ',';
    }
  }
  $usernames = rtrim($usernames, ',');
  $userids = rtrim($userids, ',');
  $sid = get_session();
  query_result($month, $orgid, $userids, $sid);
  $result = get_result($sid);
  $data = fetch_data($result['html']);
  $status = is_array($data) ? (count($data) > 0 ? 'successed' : 'empty') : 'failed';
  return [
    'session' => $sid,
    'month' => $month,
    'orgid' => $orgid,
    'status' => $status,
    'count' => $status == 'successed' ? count($data) : 0,
    'data' => $data,
    'html' => $result['html'],
  ];
}

function get_session() {
  $url = SERVER_ADDRESS . 'WebReport/ReportServer?reportlet=%25E6%2596%25B0%25E7%2589%2588_%25E7%25AE%25A1%25E5%2588%25B6%25E6%25B4%25A5%25E8%25B4%25B4%25E4%25B8%25AA%25E4%25BA%25BA%25E6%258A%25A5%25E8%25A1%25A8.cpt&op=view&userId=19&orgId=4';
  // $url = SERVER_ADDRESS . 'WebReport/ReportServer?reportlet=新版_管制津贴个人报表.cpt&op=view&userId=19&orgId=4';
  $curl = curl_init();
  curl_setopt_array($curl, array(
    CURLOPT_URL => $url,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_ENCODING => '',
    CURLOPT_MAXREDIRS => 10,
    CURLOPT_TIMEOUT => 0,
    CURLOPT_FOLLOWLOCATION => true,
    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
    CURLOPT_CUSTOMREQUEST => 'GET',
  ));
  $response = curl_exec($curl);
  curl_close($curl);
  $pattern = "/FR\.SessionMgr\.register\('(\d*)', contentPane\);/";
  preg_match($pattern, $response, $matches);
  $session = (int)$matches[1];
  return $session;
}

function query_result($month, $org_id, $user_ids, $session_id) {

  /*
  {
  "SEARCHYM": "2022-08",
  "ORGID": 13,
  "USERID2": "20120039,20160039,..."
  }
   */

  $params = ['SEARCHYM' => $month, 'ORGID' => $org_id, 'USERID2' => $user_ids];
  $parameters = urlencode(json_stringify($params));
  $curl = curl_init();
  curl_setopt_array($curl, array(
    CURLOPT_URL => SERVER_ADDRESS . "WebReport/ReportServer?op=fr_dialog&cmd=parameters_d&sessionID={$session_id}",
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_ENCODING => '',
    CURLOPT_MAXREDIRS => 10,
    CURLOPT_TIMEOUT => 0,
    CURLOPT_FOLLOWLOCATION => true,
    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
    CURLOPT_CUSTOMREQUEST => 'POST',
    CURLOPT_POSTFIELDS => "__parameters__={$parameters}",
    CURLOPT_HTTPHEADER => array(
      'Content-Type: application/x-www-form-urlencoded',
    ),
  ));
  $response = curl_exec($curl);
  curl_close($curl);
  return $response;
}

function get_result($session_id) {
  $url = SERVER_ADDRESS . "WebReport/ReportServer?op=fr_view&cmd=view_content&sessionID={$session_id}&reportIndex=0";
  $curl = curl_init();
  curl_setopt_array($curl, array(
    CURLOPT_URL => $url,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_ENCODING => '',
    CURLOPT_MAXREDIRS => 10,
    CURLOPT_TIMEOUT => 20,
    CURLOPT_FOLLOWLOCATION => true,
    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
    CURLOPT_CUSTOMREQUEST => 'GET',
  ));
  $response = curl_exec($curl);
  curl_close($curl);
  $response = iconv("gbk", "utf-8//ignore", $response);
  return ['url' => $url, 'html' => $response];
}

function fetch_data($html) {
  $data = [];
  $html = preg_replace('~<([a-z]+?)\s+?.*?>~i', '<$1>', $html);
  $tr = explode('<tr>', explode('<tbody>', $html)[5]);
  foreach ($tr as $tr_) {
    $td = explode('<td>', $tr_);
    if (count($td) == 26) {
      $row = [];
      foreach (COLUMNS as $i) {
        // $val = gettype($i) == 'integer' ? $td[$i + 1] : $td[$i[0] + 1];
        $val = $td[$i[0] + 1];
        $val = preg_replace("/\<[a-z]*\>/", '', $val);
        $val = preg_replace("/\<\/[a-z]*\>/", '', $val);
        $val = trim($val);
        $val = is_numeric($val) ? (float)$val : $val;
        // gettype($i) == 'integer' ? $row[] = $val : $row[$i[1]] = $val;
        $row[$i[1]] = $val;
      }
      $data[] = $row;
    }
  }
  return $data;
}

# http://10.202.91.29/2KT/api/hours_personal_report.php?orgid=12&month=2023-01&force=1
# http://10.202.91.29/2KT/api/hours_personal_report.php?orgid=13&month=2023-01&force=1
# http://10.202.91.29/2KT/api/hours_personal_report.php?orgid=12&month=2023-02&force=1
# http://10.202.91.29/2KT/api/hours_personal_report.php?orgid=13&month=2023-02&force=1

# http://projects:8081/cdapp-2kt-api/api/hours_personal_report.php?orgid=13&month=2023-01&force=1
# http://projects:8081/cdapp-2kt-api/api/hours_personal_report.php?orgid=13&month=2023-01&force=1
# http://projects:8081/cdapp-2kt-api/api/hours_personal_report.php?orgid=13&month=2023-01&force=1
# http://projects:8081/cdapp-2kt-api/api/hours_personal_report.php?orgid=13&month=2023-01&force=1